package org.xx.armory.spring5.jdbc;

import org.springframework.beans.factory.config.ConfigurableBeanFactory;
import org.springframework.context.annotation.Scope;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Component;
import org.xx.armory.spring5.domain.Book;

import java.math.BigDecimal;
import java.sql.Types;
import java.util.Date;
import java.util.List;

import static java.sql.Types.NVARCHAR;
import static java.sql.Types.TIMESTAMP;

@Component
@Scope(ConfigurableBeanFactory.SCOPE_PROTOTYPE)
public final class BookDao
        extends JdbcDao {
    @Override
    public void afterPropertiesSet()
            throws Exception {
        super.afterPropertiesSet();
        this.identifierCaseMode = JdbcDao.IdentifierCaseMode.UPPER;
    }

    @Override
    public BookDao setPageable(
            Pageable pageable
    ) {
        return (BookDao) super.setPageable(pageable);
    }

    public final int insert(
            String title,
            String author,
            Date pubDate,
            BigDecimal price,
            int amount
    ) {
        return setAutoKey("*")
                .update(
                        new SqlAndArgsBuilder()
                                .append("INSERT INTO BOOKS")
                                .append("(TITLE, AUTHOR, PUB_DATE, PRICE, AMOUNT)")
                                .append("VALUES (#title#, #author#, #pub_date#, #price#, #amount#)")
                                .addArg("title", title, Types.VARCHAR)
                                .addArg("author", author, Types.VARCHAR)
                                .addArg("pub_date", pubDate, Types.TIMESTAMP)
                                .addArg("price", price, Types.DECIMAL)
                                .addArg("amount", amount, Types.INTEGER)
                );
    }

    public final int update(
            long bookId,
            String title,
            String author,
            Date pubDate,
            BigDecimal price,
            int amount
    ) {
        return update(
                new SqlAndArgsBuilder()
                        .append("UPDATE BOOKS")
                        .append("  SET TITLE = #title#, AUTHOR = #author#, PUB_DATE = #pub_date#, PRICE = #price#, AMOUNT = #amount#")
                        .append("WHERE BOOK_ID = #book_id#")
                        .addArg("book_id", bookId, Types.INTEGER)
                        .addArg("title", title, Types.VARCHAR)
                        .addArg("author", author, Types.VARCHAR)
                        .addArg("pub_date", pubDate, Types.TIMESTAMP)
                        .addArg("price", price, Types.DECIMAL)
                        .addArg("amount", amount, Types.INTEGER));
    }

    public Book findById(
            long bookId
    ) {
        return querySingle(
                new SqlAndArgsBuilder()
                        .append("SELECT BOOK_ID, TITLE, AUTHOR, PUB_DATE, PRICE, AMOUNT")
                        .append("FROM BOOKS")
                        .append("WHERE BOOK_ID = #book_id#")
                        .addArg("book_id", bookId, Types.INTEGER),
                new BookMapper());
    }

    public List<Book> findAll(
            String key,
            Date startDate,
            Date endDate
    ) {
        return queryList(
                new SqlAndArgsBuilder()
                        .append("SELECT " + String.join(", ", quoteIdentifiers("B", Book.ALL_FIELD_NAMES)))
                        .append("FROM BOOKS B")
                        .dynamic("WHERE ", "\n  AND ", "")
                        .addIfNotBlank(key, "(TITLE LIKE #key# OR AUTHOR LIKE #key#)")
                        .addIfNotNull(startDate, "PUB_DATE >= #start_date#")
                        .addIfNotNull(endDate, "PUB_DATE < #end_date#")
                        .end()
                        .addArg("key", escapeContains(key), NVARCHAR)
                        .addArg("start_date", startDate, TIMESTAMP)
                        .addArg("end_date", endDate, TIMESTAMP),
                new BookMapper());
    }

    public int count(
            String key,
            Date startDate,
            Date endDate
    ) {
        return queryInt(
                new SqlAndArgsBuilder()
                        .append("SELECT COUNT(1)")
                        .append("FROM BOOKS")
                        .dynamic("WHERE ", "\n  AND ", "")
                        .addIfNotBlank(key, "(TITLE LIKE #key# OR AUTHOR LIKE #key#)")
                        .addIfNotNull(startDate, "PUB_DATE >= #start_date#")
                        .addIfNotNull(endDate, "PUB_DATE < #end_date#")
                        .end()
                        .addArg("key", escapeContains(key), NVARCHAR)
                        .addArg("start_date", startDate, TIMESTAMP)
                        .addArg("end_date", endDate, TIMESTAMP)
        );
    }
}
